package kr.co.choongang;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class EmpDAO {

	DataSource dataFactory;
	
	
	/*String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	String userid = "rhkss8";
	String passwd = "rlaxodus1"*/;

	public EmpDAO() {
		
		try {
			Context ctx = new InitialContext();
			dataFactory = (DataSource)ctx.lookup("java:comp/env/jdbc/Oracle11g");
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		/*try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}*/
	}

	public ArrayList<EmpDTO> select() {
		ArrayList<EmpDTO> list = new ArrayList<EmpDTO>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			con = dataFactory.getConnection();
			String sql = "select emp_id,ename,salary,depart from emp";
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				String emp_id = rs.getString("emp_id");
				String ename = rs.getString("ename");
				int salary = rs.getInt("salary");
				String depart = rs.getString("depart");

				EmpDTO dto = new EmpDTO(emp_id, ename, salary, depart);
				list.add(dto);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (pstmt != null)
					pstmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public int insert(EmpDTO dto) {

		Connection con = null;
		PreparedStatement pstmt = null;
		int num = 0;

		try {
			con = dataFactory.getConnection();
			String sql = "insert into emp(emp_id,ename,salary,depart) values(?,?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getEmp_id());
			pstmt.setString(2, dto.getEname());
			pstmt.setInt(3, dto.getSalary());
			pstmt.setString(4, dto.getDepart());

			num = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return num;
	}

	public int delete(String delete) {

		Connection con = null;
		PreparedStatement pstmt = null;
		int num = 0;

		try {
			con = dataFactory.getConnection();
			String sql = "delete from emp where emp_id = (?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, delete);

			num = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;
	}

	public int edit(String edit, EmpDTO dto) {
		Connection con = null;
		PreparedStatement pstmt = null;

		int num = 0;

		try {
			con = dataFactory.getConnection();
			String sql = "update emp set emp_id=?, ename=?,salary=?,depart=? where emp_id=?";

			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, dto.getEmp_id());
			pstmt.setString(2, dto.getEname());
			pstmt.setInt(3, dto.getSalary());
			pstmt.setString(4, dto.getDepart());
			pstmt.setString(5, edit);

			num = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return num;

	}
}
